﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BookStore.Common;
using System.Data.SqlClient;
using BookStore.Models;
using System.Data;
using System.Windows.Forms;

namespace BookStore.Controllers
{
    public class ManufactureController : Controller
    {
        private static ManufactureController _controller = null;
        public static ManufactureController getController()
        {
            if (_controller == null)
                return new ManufactureController();
            else
                return _controller;
        }
        public ManufactureController()
        {
            _controller = this;
        }


        internal object getAllManufacture()
        {
            DataTable dt = new DataTable();
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("Select * from MANUFACTURER", con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
            }
            finally
            {
                con.Close();
            }
            return dt;
        }

        public String getManufacturer(String manufacturer, int check)
        {
            DataTable dt = new DataTable("GetManufacturer");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Get ManufacturerName from ManufacturerID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_GetManufacturerName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = manufacturer;
                }
                // Get ManufacturerID from ManufacturerName, check = 1
                else
                {
                    cmd = new SqlCommand("BS_GetManufacturerID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ManufacturerName", SqlDbType.NVarChar).Value = manufacturer;
                }
                var result = cmd.ExecuteReader();
                dt.Load(result);
                if(dt.Rows.Count > 0)
                    return dt.Rows[0][0].ToString();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return "";
        }

        public void AddManufacturerToDatabase(String ManufacturerID, String ManufacturerName)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_AddManufacturerToDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = ManufacturerID;
                cmd.Parameters.Add("@ManufacturerName", SqlDbType.NVarChar).Value = ManufacturerName;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void DeleteManufacturerFromDatabase(String ManufacturerID)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_DeleteManufacturerFromDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = ManufacturerID;
                cmd.ExecuteNonQuery();
                tran.Commit();
                MessageBox.Show("Deleted successful!", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
                MessageBox.Show("You don't permission to delete this manufacturer because it's attribute!", "Error : Delete Manufacturer", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void ModifyManufacturerFromDatabase(String ManufacturerID, String ManufacturerName)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_ModifyManufacturerFromDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = ManufacturerID;
                cmd.Parameters.Add("@ManufacturerName", SqlDbType.NVarChar).Value = ManufacturerName;
                cmd.ExecuteNonQuery();
                tran.Commit();
                MessageBox.Show("Modified successful!", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
                MessageBox.Show("You don't permission to modify this manufacturer because it's attribute!", "Error : Modify Manufacturer", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public DataTable DisplayManufacturer(String Manufacturer, int check)
        {
            DataTable dt = new DataTable("DisplayManufacturer");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Display Manufacturer From ManufacturerID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_DisplayManufacturerFromID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = Manufacturer;
                }
                // Display Manufacturer From ManufacturerName, check = 1
                else if (check == 1)
                {
                    cmd = new SqlCommand("BS_DisplayManufacturerFromName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ManufacturerName", SqlDbType.NVarChar).Value = Manufacturer + "%";
                }
                // Display Manufacturer like ManufacturerID, check = 2
                else
                {
                    cmd = new SqlCommand("BS_DisplayManufacturerLikeID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = Manufacturer + "%";
                }
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public DataTable DisplayAllManufacturer()
        {
            DataTable dt = new DataTable("DisplayAllManufacturer");
            String sql = @"SELECT [ManufacturerID],[ManufacturerName]
                           FROM [BookStore].[dbo].[MANUFACTURER]";
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public Boolean CheckManufacturerInDatabase(String Manufacturer, int check)
        {
            DataTable dt = new DataTable("CheckLocationInDatabase");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Check Manufacturer From ManufacturerID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_CheckManufacturerFromID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = Manufacturer;
                }
                // Check Manufacturer From ManufacturerName, check = 1
                else
                {
                    cmd = new SqlCommand("BS_CheckManufacturerFromName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ManufacturerName", SqlDbType.NVarChar).Value = Manufacturer;
                }
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                if (dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return false;
        }

        public Boolean CheckManufactuerName(String ManufactuerName)
        {
            for (int i = 0; i < ManufactuerName.Length; i++)
            {
                if ((ManufactuerName[i].ToString() == "\\") || (ManufactuerName[i].ToString() == ";") || (ManufactuerName[i].ToString() == ")"))
                    return false;
            }
            return true;
        }

        public String GetNumericCodeOfManufacturer(String codeClassify)
        {
            String ManufacturerID;
            String convert;
            String code;
            Decimal numeric;
            con = BSCommon.getConnection();
            DataTable dt = new DataTable("GetNumericCodeOfManufacturer");
            try
            {
                con.Open();
                cmd = new SqlCommand("BS_GetManufacturerIDLikeID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = codeClassify + "%";
                var result = cmd.ExecuteReader();
                dt.Load(result);
                if (dt.Rows.Count > 0)
                {
                    ManufacturerID = dt.Rows[0][0].ToString();
                    convert = ManufacturerID.Substring(3, 4);
                    // if ManufacturerID begin not 0001
                    if (convert != "0001")
                        return "0001";
                    else
                    {
                        numeric = Convert.ToDecimal(convert);
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            int j = convert.Length - numeric.ToString().Length;
                            ManufacturerID = convert.Substring(0, j); // get numbers of "0" front of numeric
                            code = Convert.ToString(ManufacturerID + numeric);
                            if (dt.Rows[i][0].ToString().Substring(3, 4) != code)
                                return code; // return numerics of ManufacturerID
                            numeric += 1; // Increase numeric 
                        }
                        String temp = numeric.ToString();
                        while (temp.Length < 4)
                        {
                            temp = "0" + temp;
                        }
                        return temp;
                    }
                }
                else
                    return "0001";
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }
   }
}
